1-2-3 Notes All Versions September 6, 1984 Lotus Development Corporation NOTE 102 Memory Use Introduction A 1-2-3 worksheet has 256 columns and 2,048 rows (or 523,288 cells), but the actual amount of information you can enter on one worksheet depends on the amount of available memory. Available memory depends on four factors: * the amount of Random Access Memory (RAM) in the computer * the organization of the worksheet * the type of data you are entering * the amount of information you enter in each cell This note examines some basic aspects of 1-2-3 and memory use. Random Access Memory Random Access Memory, or RAM, is the amount of temporary memory available in the computer. The memory is temporary because information is stored in RAM only when the computer is turned on. If you turn the computer off, all the information in RAM is lost. (To permanently store information, you save it on a floppy disk or hard disk.) RAM is measured in bytes. One kilobyte, or 1K, is equal to 1,024 bytes. If a computer has 256K, it has 256 kilobytes (or 262,144 bytes) of RAM. Think of RAM as spaces in the computer for storing information while you are working. The larger the RAM, the more bytes, or spaces, are available for use with a worksheet. The /Worksheet Status Command The /Worksheet Status command displays information about the worksheet settings and the amount of available memory on a worksheet. Begin with a new 1-2-3 worksheet on the screen and select the /Worksheet Status command. The headings for the worksheet settings and available memory appear at the top of the screen. The third line displays the status of the settings and memory. The number under Avail Memory is the amount of RAM (in bytes) available for use with the worksheet. (This is not the available space on the disk.) On a new worksheet, the Avail Memory status reflects the amount of RAM in the computer minus the memory used by 1-2-3 and the operating system. The available memory will vary depending on which version of 1-2-3 you are using, the size of the operating system, and the particular drivers you have installed. 1-2-3 requires approximately 100K, and the operating system may need from 12K to 30K depending on the system. The remaining memory is available for a worksheet, and the amount of available memory will decrease as you enter information on the worksheet. Note: 1-2-3 does not support the use of the Assign command in DOS. If you have used DOS's Assign command, the available memory status may be over several megabytes. If you add memory to the computer, the increase will automatically be reflected by an increased amount of memory available for the worksheet. The amount of memory you can add varies from computer to computer. Worksheet Organization and Memory Use The way information is organized on a worksheet helps determine how much memory is needed. 1-2-3 defines the "active worksheet area" as a rectangle extending from cell A1 to the last column and row where information was entered or a cell was formatted. You can identify this active area by pressing the End key followed by the Home key. This moves the pointer to the cell in the lower-right corner of the active area. It is important to know the size of the active area because 1-2-3 allocates 4 bytes of memory for every blank cell that falls within the active area of the worksheet. For example, if you entered information in cell T20, 1-2-3 would define the active area as the range from A1 to T20. Memory would be allocated for that range even though you entered information in only one cell. If you pressed the End key followed by the Home key, the pointer would go to cell T20. Since blank cells in the active area take up memory, you should make the active area of the worksheet as compact and close to A1 as possible. Note: If you are creating a worksheet containing a macro, keep in mind that blank rows and columns between the macro and the worksheet use up memory. Although you should enter a macro in a blank area of the worksheet, choose a spot that will keep the active area of the worksheet as small as possible. Using End-Home to Evaluate the Active Area The following exercise uses the End and Home keys to examine the relationship between worksheet size, organization, and memory use. 1. Start with an empty worksheet. Move the pointer to any cell but do not enter any information. To check the End-Home position, press the End key and then the Home key. The pointer should move to cell A1 because you have not entered any information on the worksheet. 2. Select /Worksheet Status. Look under Avail Memory and write down the number of bytes available on the empty worksheet. Press the Return key to return to Ready Mode. 3. Go to cell E1 and enter a single letter. Move the pointer back to cell A1. Now press the End and Home keys. The pointer should move to cell E1 because 1-2-3 has defined the active area of the worksheet as the range from A1 to E1. 4. Select /Worksheet Status again and look at the available memory. 1-2-3 has used 4 bytes for every empty cell in the active area between cells A1 and E1. Write down how much memory is now available. Press Return. 5. Go to cell A10 and enter a single letter. Press the End and Home keys, and the pointer should move to cell E10. Remember, 1-2-3 defines the active area as a rectangle extending from cell A1 to the last column and row where information was entered. 1-2-3 now defines the active area as the range from A1 to E10. 6. Select /Worksheet Status again and look at the available memory. Compare the number under Avail Memory to the other two numbers you wrote down. 1-2-3 has used 4 bytes for every blank cell in the active area between cells A1 and E10. Press the Return key to return to Ready Mode. Using /File Xtract to Adjust the Active Area Sometimes the End-Home position is not where you expect it to be. If you have placed and then erased information or accidently typed a character in a remote cell, the End-Home position may be far from the worksheet data and you may get a Memory Full error message. Memory Full means there is no more memory (RAM) available for use with the worksheet. Erasing the contents of a cell does not change the active area of the worksheet, so erasing a cell or even deleting several rows will not increase the available memory. To decrease the active area of a worksheet and regain memory, use the /File Xtract command. /File Xtract copies all of the information in a specified range of the worksheet to a separate, new worksheet file. The active area of this new worksheet file includes only the range of information you copied. Note: If you are doing a /File Xtract on a worksheet, be sure to include all the information you want copied into the new file. Also, choose a new file name. If you choose an existing file name, the worksheet you are extracting will replace the existing file. See the 1-2-3 User's Manual for more information on /File Xtract. 1. Start with a new worksheet and enter the following information, beginning in cell A1: A B C 1 1984 Jan. Feb. 2 Rent 550 550 3 Food 400 350 4 Car 180 40 5 Medical 65 0 2. Press the End and Home keys. The pointer should move to cell C5. Select /Worksheet Status and write down the amount of available memory. 3. Go to cell E10 and enter a number or letter. Go back to cell A1 and press the End and Home keys to identify the active area of the worksheet. Select /Worksheet Status and write down the amount of available memory. 4. Go to cell E10 and use the /Range Erase command to erase the information there. Move back to cell A1 and press End and Home again. Even though you erased the information in cell E10, 1-2-3 remembers that information was placed there and does not change the active area of the worksheet. Select /Worksheet Status. You will see you have not regained any memory by erasing cell E10. 5. Move the pointer to cell A1 and select /File Xtract. Select Formulas. 6. Type in a new file name. Name the new file Memory for this example. Press Return. 7. Now specify the worksheet range that you want to extract into the new file, Memory. The range should include only cells with relevant information (A1 to C5 in this example). Do not include the blank cells below or to the right of the worksheet data. 8. Press Return, and 1-2-3 will extract the range you specified and make a new worksheet file. 9. To see the new file, select /File Retrieve and choose the file named Memory. Check the size of the active area of the new file by pressing End and Home. The End-Home position should be cell C5. Select /Worksheet Status and check the available memory. It should be greater than the amount of memory you recorded before extracting the file. Worksheet Content and Memory Use The amount of information you can enter on a worksheet depends on the content of the cells as well as the organization of the data. The following information should help you evaluate memory use in a worksheet: * Blank cells use 4 bytes. * Integers (numbers between -32768 and 32768) use 4 bytes. * Real numbers use 12 bytes. * Labels up to 4 characters long use 6 bytes. Longer labels use an additional byte for each additional character. For example, a 3-character label would use 6 bytes, and a 10-character label would use 12 bytes. * Formulas use a minimum of 30 bytes. The more complex a formula, the more memory it uses. * Range names use 32 bytes each, regardless of the length of the name. * Graph names use 461 bytes each, regardless of which options are used.